#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time    : 2025/2/5 17:59
# @Author  : Fancy
# @FileName: comparator.py
# @Software: PyCharm
# @runstate    ：
# database_comparator.py
from db_compare.clients.mysql import MySQLClient
from db_compare.clients.dm import DMClient
from db_compare.clients.postgres import PGClient
from db_compare.config import DBConfig
from db_compare.clients.base import BaseClient
from db_compare.utils.logger import setup_logger
from db_compare.utils.reports import ReportGenerator
from typing import Dict,List
import re

logger = setup_logger (__name__)


class DatabaseComparator:
    def __init__ (self, source_config: DBConfig, target_config: DBConfig):
        self.source_config = source_config
        self.target_config = target_config
        self.source = BaseClient.create (source_config)  # 动态创建客户端
        self.target = BaseClient.create (target_config)  # 动态创建客户端
        self.reporter = ReportGenerator ()
        self.type_map = {
            # 通用类型
            'VARCHAR': 'STRING',
            'VARCHAR2': 'STRING',  # 达梦特有
            'CHAR': 'STRING',
            'TEXT': 'STRING',
            'CLOB': 'STRING',  # 达梦大文本

            # 数值类型
            'INT': 'INTEGER',
            'INTEGER': 'INTEGER',
            'BIGINT': 'BIGINT',
            'NUMBER': 'DECIMAL',  # 达梦数字类型
            'DECIMAL': 'DECIMAL',

            # 时间类型
            'DATE': 'DATE',
            'DATETIME': 'TIMESTAMP',
            'TIMESTAMP': 'TIMESTAMP',

            # 二进制类型
            'BLOB': 'BINARY',
            'BINARY': 'BINARY',

            # 达梦特殊类型
            'BFILE': 'FILE_REF',  # 外部文件引用
            'IMAGE': 'BINARY'  # 图像类型
        }



        # 类型标准化规则
        self.type_map = {
            'VARCHAR': 'VARCHAR',
            'VARCHAR2': 'VARCHAR',  # 达梦的 VARCHAR2 转为 VARCHAR
            'CHAR': 'CHAR',
            'TEXT': 'TEXT',
            'CLOB': 'TEXT',
            'INT': 'INTEGER',
            'INTEGER': 'INTEGER',
            'BIGINT': 'BIGINT',
            'NUMBER': 'DECIMAL',  # 达梦的数字类型统一为 DECIMAL
            'DECIMAL': 'DECIMAL',
            'DATE': 'DATE',
            'DATETIME': 'TIMESTAMP',
            'TIMESTAMP': 'TIMESTAMP',
            'BLOB': 'BINARY',
            'BINARY': 'BINARY'
        }

    # def _normalize_type (self, raw_type: str) -> str:
    #     """处理类型差异并校验长度"""
    #     base_type = next (
    #         (k for k in self.type_map if k in raw_type.upper ()),
    #         raw_type.split ('(')[0].upper ()
    #     )
    #     return self.type_map.get (base_type, base_type)
    #专门为了达梦设置的类型处理
    def _normalize_type (self, raw_type: str) -> str:
        """增强达梦类型处理"""
        base_type = re.sub (r'\(\s*,\s*\d+\)', '', raw_type)  # 处理 VARCHAR(,7) 或类似格式
        base_type = re.sub (r'\(\d+\)', '', base_type).strip ().upper ()  # 去除括号内的数字，统一为大写

        #处理达梦的 NUMBER 类型精度
        if base_type == 'NUMBER':
            if 'PRECISION' in raw_type:
                return 'DECIMAL'
            else:
                return 'INTEGER' if ',0)' in raw_type else 'DECIMAL'

        return self.type_map.get (base_type,base_type)

    def _get_tables (self, client: BaseClient) -> List[str]:

        """获取当前连接的数据库中的所有表"""
        try:
            conn = client.get_conn ()  # 连接数据库
            cursor = conn.cursor ()

            if isinstance (client, DMClient):
                cursor.execute ("""
                        SELECT TABLE_NAME 
                        FROM ALL_TABLES 
                        WHERE OWNER = 'SMART_LOCK_API_BASIC'
                    """)
            elif isinstance (client, MySQLClient):
                cursor.execute ("SHOW TABLES")
            else:
                raise ValueError ("Unsupported database type")

            # **重要：fetchall 只能调用一次**
            rows = cursor.fetchall ()
            print (f"查询 {client.__class__.__name__} 结果: {rows}")  # 打印返回的表名列表

            # **转换表名为小写，确保跨数据库对比**
            tables = [row[0].lower () for row in rows]
            return tables
        except Exception as e:
            logger.error (f"获取表列表失败: {e}")
            raise
        finally:
            conn.close ()  # **确保连接被关闭**

            # # 判断数据库类型
            # client_type = client.__class__.__name__
            #
            # if "MySQL" in client_type:  # 处理 MySQL
            #     cursor.execute ("SHOW TABLES;")  # 直接获取当前数据库的所有表
            # elif "PG" in client_type:  # 处理 PostgreSQL
            #
            #     cursor.execute ("SELECT tablename FROM pg_tables WHERE schemaname = 'public';")
            # else:
            #     raise ValueError (f"未知的数据库类型: {client_type}")
            #
            # tables = [row[0] for row in cursor.fetchall ()]
            # return tables

    def _get_table_record_count (self, client: BaseClient, table: str) -> int:
        """获取指定表的记录数，统一使用小写或大写，根据数据库类型调整"""
        try:
            conn = client.get_conn ()
            cursor = conn.cursor ()
            if isinstance (client, DMClient):
                query_table = f'{table.upper ()}'
                #达梦数据库增加一个库名，mysql不用 ‘SMART_LOCK_API_BASIC.’
                cursor.execute(f"SELECT COUNT(*) FROM SMART_LOCK_API_BASIC.{query_table}")
            elif isinstance (client, MySQLClient):
                # 对于 MySQL，假设表名全部为小写
                query_table = table.lower()
                cursor.execute (f"SELECT COUNT(*) FROM {query_table}")
            else:
                raise ValueError("Unsupported database type")
            print (f"执行的查询: SELECT COUNT(*) FROM {query_table}")

            result = cursor.fetchone ()
            print(result)

            if result is None:
                logger.warning (f"表 {table} 中没有数据或表不存在")
                return 0
            count = result[0]
            conn.close ()
            return count if count != 0 else 0

        except Exception as e:
            logger.error (f"获取 {table} 记录数失败: {e}")
            return 0


    def _get_columns (self, client: BaseClient, table: str) -> Dict[str, str]:
        """获取表的元数据"""
        try:
            # 假设 get_conn 方法返回数据库连接
            conn = client.get_conn ()
            cursor = conn.cursor ()
            # 判断数据库类型
            if isinstance (client, DMClient):  # 达梦特有查询
                cursor.execute (f"""
                            SELECT 
                                COLUMN_NAME, 
                                DATA_TYPE || 
                                CASE WHEN DATA_SCALE > 0 
                                    THEN '(' || DATA_PRECISION || ',' || DATA_SCALE || ')' 
                                    WHEN DATA_LENGTH > 0 
                                    THEN '(' || DATA_LENGTH || ')' 
                                    ELSE '' 
                                END AS FULL_TYPE
                            FROM ALL_TAB_COLUMNS 
                            WHERE TABLE_NAME = '{table.upper ()}'
                        """)
            elif isinstance (client, MySQLClient):
                cursor.execute (f"DESCRIBE {table}")
            else:
                raise ValueError ("Unsupported database type")
                # **将字段名转换为小写**
            #columns = [(row[0].strip().lower(), row[1].strip()) for row in cursor.fetchall()]3
            columns = {row[0].lower (): self._normalize_type (row[1]) for row in cursor.fetchall ()}
            conn.close ()
            return columns

            # # 判断数据库类型
            # client_type = client.__class__.__name__
            # if "MySQL" in client_type:
            #     cursor.execute (f"DESCRIBE {table}")  # MySQL 的 DESCRIBE 命令，获取表结构
            # elif "PG" in client_type:
            #     cursor.execute (f"""
            #                     SELECT column_name, data_type
            #                     FROM information_schema.columns
            #                     WHERE table_schema = 'public' AND table_name = '{table}';
            #                 """)
            # columns = {row[0]: row[1] for row in cursor.fetchall ()}
            # conn.close ()
            # return columns

        except Exception as e:
            logger.error (f"获取表结构失败: {e}")
            raise



    def compare_data (self,keys: List[str] = None):
        try:
            # 获取表列表
            mysql_tables = self._get_tables (self.source)  # 只获取默认数据库的表
            #pg_tables = self._get_tables (self.target)  # 只获取默认数据库的表
            dm_tables = self._get_tables (self.target)

            # 找出相同的表
            common_tables = set (mysql_tables) & set (dm_tables)
            logger.info (f"相同的表: {common_tables}")

            all_diff = {}

            for table in common_tables:
                try:
                    src_cols = self._get_columns (self.source, table)
                    tgt_cols = self._get_columns (self.target, table)
                    # 将列名和类型都统一为小写和标准化类型
                    src_cols = {col.lower (): self._normalize_type (type_) for col, type_ in src_cols.items ()}
                    tgt_cols = {col.lower (): self._normalize_type (type_) for col, type_ in tgt_cols.items ()}

                    diff = {
                        'missing_in_target': [col for col in src_cols if col not in tgt_cols],
                        'missing_in_source': [col for col in tgt_cols if col not in src_cols],
                        'type_mismatch': [
                            (col, f"{src_cols[col]}", f"{tgt_cols[col]}")
                            for col in src_cols
                            if col in tgt_cols and src_cols[col] != tgt_cols[col]
                        ]
                    }
                    # 获取表记录数差异
                    source_count = self._get_table_record_count (self.source, table)
                    target_count = self._get_table_record_count (self.target, table)
                    print("sc:",source_count)
                    print("tc:",target_count)

                    if source_count != target_count:
                        diff['missing_source_count'] = [
                            (table, source_count, target_count)
                        ]
                    if any (diff.values ()):
                        all_diff[table] = diff
                        logger.info (f"{table} 的元数据比对完成")

                except Exception as e:
                    logger.error (f"{table} 的比对失败: {e}")

            #report_path = self.reporter.save_json(all_diff, "metadata_diff_report")
            report_path= self.reporter.save_csv(all_diff,"metadata_diff_report")
            logger.info (f"比对报告已生成: {report_path}")
            return all_diff

        except Exception as e:
            logger.error (f"元数据比对失败: {e}")
            raise

